package edu.westga.cs.recommender.data;

import java.sql.*;
import java.util.LinkedList;

/**
 * Data class for handling a MySQL database connection.
 * 
 * DO NOT MESS WITH THIS CLASS - Errors here will break everything "upstream", and the code does not
 * need to be changed to handle customization elsewhere in the code.
 * 
 * The only reason to mess with this is if the underlying database implementation changes.  A text file
 * has been provided in the notes directory with the necessary information.
 * @author Brent W. Ezell
 *
 */
public class MySQLDatabase {
	//Fields
	
	//Constructor
	/**
	 * Sets up the driver.
	 */
	/// Notes: Do NOT change the driver.
	public MySQLDatabase() {
		// Get the driver.
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Primary helper method - opens database connection.
	 */
	private Connection openConnection() {
		// Open the connection.
		Connection conn = null;
		try {
			conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/recom_test?"+ 
					"user=recom_reader&password=nil");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * Primary helper method - closes an open database connection.
	 */
	private void closeConnection(Connection conn) {
		// Close the connection.
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Main method for the class.  Processes an input query and hands the results back.
	 * @param query The valid SQL query to process.  This HAS to be correct - check against the server
	 * 			directly somewhere else before putting it in the code!
	 * @return A LinkedList<String> containing the results.
	 */
	public LinkedList<String> getData(String query) {
		// Set it to null to humor the compiler's complaints about possible non-initialization.
		Connection conn = openConnection();
		// Create the statement.
		Statement stmt = null;
		try {
			stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// Creates a result set from the query, with null initial value to satisfy compiler.
		ResultSet srs = null;
		try {
			srs = stmt.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// Gets the number of rows in the result.
		int size = -1;
		boolean continueLoop = true;
		do {
			size += 1;
			try {
				continueLoop = srs.next();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} while (continueLoop == true);
		// Builds the return list.
		int columns = 0;
		try {
			columns = srs.getMetaData().getColumnCount();
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		int oversize = columns+1;
		LinkedList<String> output = new LinkedList<String>();
		try {
			srs.first();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		for (int n = 0; n < size; n++) {
			for (int num = 1; num < oversize; num++) {
				try {
					output.add(srs.getString(num));
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			try {
				srs.next();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection(conn);
		return output;
	}
}
